import pymysql
import xlrd
from xlrd import xldate_as_tuple
import datetime

# 连接数据库
try:
    db = pymysql.connect(host='127.0.0.1',
                         user='root',
                         passwd='mysql',
                         db='study',
                         charset='utf8')
    print('mysql连接成功!')
except:
    print('mysql连接失败!')


def open_excel():
    try:
        school = xlrd.open_workbook('私有云-华北电力大学国家大学科技园-企业资质.xlsx')
    except Exception:
        print("打开excel失败!")
    try:
        sheet = school.sheet_by_name('Sheet2')
        return sheet
    except Exception:
        print('表名不存在!')


def insert_count():
    sheet = open_excel()
    cursor = db.cursor()
    # excel第一行为标签名，对应python中下标为0
    for i in range(1, sheet.nrows):
        company_name = sheet.cell(i, 0).value  # 取第i行0列的值
        date_time = sheet.cell(i, 1).value
        if date_time:
            date_time = datetime.datetime(*xldate_as_tuple(float(date_time), 0)).strftime('%Y-%m-%d')
        batch = sheet.cell(i, 2).value
        qualification = sheet.cell(i, 3).value
        value = (company_name, date_time, batch, qualification)
        print(date_time)
        sql = 'INSERT INTO company(company_name,date_time,batch,qualification) values (%s,%s,%s,%s)'
        cursor.execute(sql, value)
        db.commit()
    cursor.close()
    print('插入成功!')


insert_count()



